# -*- coding:utf-8 -*-
__version__ = '1.0.0.0'
"""
@brief  :   简介
@details:   详细信息
@author :   zheng
@date   :   2020-04-07
"""
import os
import time
import xlrd
import vislib
from rest_framework.views import APIView
from rest_framework.response import Response
from django.db import connection
from django.contrib.auth.models import User
from .serializers import *
from .models import *
from django.conf import settings
from datetime import date
from django.utils.decorators import method_decorator
from django.views.decorators.csrf import ensure_csrf_cookie



class ImportExcelView(APIView):

    @staticmethod
    def format_date(order_date, workbook):
        order_date = xlrd.xldate_as_tuple(order_date, workbook.datemode)
        order_date = date(*order_date[:3]).strftime('%Y-%m-%d')
        return order_date

    @staticmethod
    def import_one(obj):
        sub_file_path = obj.file_path
        file_path = os.path.join(settings.MEDIA_ROOT, sub_file_path)
        # file_path = file_path.decode('utf-8')
        # 获取数据
        workbook = xlrd.open_workbook(file_path)
        # 获取sheet 此处有图注释（见图1）
        table = workbook.sheet_by_name('Sheet1')
        # 获取总行数
        nrows = table.nrows
        for row in range(1, nrows):
            order_date = ImportExcelView.format_date(table.cell(row, 0).value, workbook)
            cus_name = table.cell(row, 1).value
            cus_type = table.cell(row, 2).value
            sales_vol = table.cell(row, 3).value
            sales_num = table.cell(row, 4).value
            discount = table.cell(row, 5).value
            profit = table.cell(row, 6).value
            order_category = table.cell(row, 7).value
            cate_items = table.cell(row, 8).value
            order_name = table.cell(row, 9).value
            iss_date = ImportExcelView.format_date(table.cell(row, 10).value, workbook)
            area = table.cell(row, 11).value
            city = table.cell(row, 12).value

            obj_excel = SalesData.objects.create(order_date=order_date,cus_name=cus_name, cus_type=cus_type,sales_vol=sales_vol,
                                     sales_num=sales_num,discount=discount,profit=profit,order_category=order_category,
                                     cate_items=cate_items,order_name=order_name,iss_date=iss_date,area=area,city=city)
            print(obj_excel)



    @staticmethod
    def import_two(obj):
        sub_file_path = obj.file_path
        file_path = os.path.join(settings.MEDIA_ROOT, sub_file_path)
        # file_path = file_path.decode('utf-8')
        # 获取数据
        workbook = xlrd.open_workbook(file_path)
        # 获取sheet 此处有图注释（见图1）
        table = workbook.sheet_by_name('Sheet1')
        # 获取总行数
        nrows = table.nrows
        for row in range(1, nrows):
            cate_items = table.cell(row, 0).value
            order_category = table.cell(row, 1).value
            sales_vol = table.cell(row, 2).value
            profit = table.cell(row, 3).value
            profit_margin = table.cell(row, 4).value
            sales_num = table.cell(row, 5).value
            cus_num = table.cell(row, 6).value
            per_order = table.cell(row, 7).value
            avg_price = table.cell(row, 8).value
            obj_excel = CategoryAnalysis.objects.create(cate_items=cate_items, order_category=order_category,
                                                        sales_vol=sales_vol,profit=profit,profit_margin=profit_margin,
                                                        sales_num=sales_num, cus_num=cus_num,per_order=per_order,
                                                        avg_price=avg_price)
            print(obj_excel)

    @staticmethod
    def import_three(obj):
        sub_file_path = obj.file_path
        file_path = os.path.join(settings.MEDIA_ROOT, sub_file_path)
        # file_path = file_path.decode('utf-8')
        # 获取数据
        workbook = xlrd.open_workbook(file_path)
        # 获取sheet 此处有图注释（见图1）
        table = workbook.sheet_by_name('Sheet1')
        # 获取总行数
        nrows = table.nrows
        for row in range(1, nrows):
            cus_name = table.cell(row, 0).value
            cus_type = table.cell(row, 1).value
            consumption = table.cell(row, 2).value
            profit = table.cell(row, 3).value
            profit_margin = table.cell(row, 4).value
            order_num = table.cell(row, 5).value
            unit_price = table.cell(row, 6).value
            city_num = table.cell(row, 7).value
            firstyear = table.cell(row, 8).value
            obj_excel = CustomerAnalysis.objects.create(cus_name=cus_name, cus_type=cus_type,
                                                        consumption=consumption, profit=profit, profit_margin=profit_margin,
                                                        order_num=order_num, unit_price=unit_price, city_num=city_num,
                                                        firstyear=firstyear)

    @staticmethod
    def import_four(obj):
        sub_file_path = obj.file_path
        file_path = os.path.join(settings.MEDIA_ROOT, sub_file_path)
        # file_path = file_path.decode('utf-8')
        # 获取数据
        workbook = xlrd.open_workbook(file_path)
        # 获取sheet 此处有图注释（见图1）
        table = workbook.sheet_by_name('Sheet1')
        # 获取总行数
        nrows = table.nrows
        for row in range(1, nrows):
            order_date = ImportExcelView.format_date(table.cell(row, 0).value, workbook)
            order_category = table.cell(row, 1).value
            cate_items = table.cell(row, 2).value
            order_name = table.cell(row, 3).value
            delivery_days = table.cell(row, 4).value
            area = table.cell(row, 5).value
            obj_excel = LogisticsAnalysis.objects.create(order_date=order_date, order_category=order_category,
                                                         cate_items=cate_items, order_name=order_name,
                                                         delivery_days=delivery_days, area=area)

    @staticmethod
    def import_five(obj):
        sub_file_path = obj.file_path
        file_path = os.path.join(settings.MEDIA_ROOT, sub_file_path)
        # file_path = file_path.decode('utf-8')
        # 获取数据
        workbook = xlrd.open_workbook(file_path)
        # 获取sheet 此处有图注释（见图1）
        table = workbook.sheet_by_name('Sheet1')
        # 获取总行数
        nrows = table.nrows
        for row in range(1, nrows):
            area = table.cell(row, 0).value
            area_manager = table.cell(row, 1).value
            sales_vol = table.cell(row, 2).value
            profit = table.cell(row, 3).value
            profit_margin = table.cell(row, 4).value
            cus_num = table.cell(row, 5).value
            unit_price = table.cell(row, 6).value
            per_order = table.cell(row, 7).value
            sales_num = table.cell(row, 8).value
            obj_excel = DealerAnalysis.objects.create(area=area, area_manager=area_manager,
                                                      sales_vol=sales_vol, profit=profit,
                                                      profit_margin=profit_margin, cus_num=cus_num,
                                                      unit_price=unit_price, per_order=per_order,
                                                      sales_num=sales_num)

    @staticmethod
    def import_six(obj):
        sub_file_path = obj.file_path
        file_path = os.path.join(settings.MEDIA_ROOT, sub_file_path)
        # file_path = file_path.decode('utf-8')
        # 获取数据
        workbook = xlrd.open_workbook(file_path)
        # 获取sheet 此处有图注释（见图1）
        table = workbook.sheet_by_name('Sheet1')
        # 获取总行数
        nrows = table.nrows
        for row in range(1, nrows):
            order_date = ImportExcelView.format_date(table.cell(row, 0).value, workbook)
            cus_name = table.cell(row, 1).value
            cus_type = table.cell(row, 2).value
            sales_vol = table.cell(row, 3).value
            sales_num = table.cell(row, 4).value
            discount = table.cell(row, 5).value
            profit = table.cell(row, 6).value
            order_category = table.cell(row, 7).value
            cate_items = table.cell(row, 8).value
            order_name = table.cell(row, 9).value
            iss_date = ImportExcelView.format_date(table.cell(row, 10).value, workbook)
            area = table.cell(row, 11).value
            city = table.cell(row, 12).value

            obj_excel = OrderDetails.objects.create(order_date=order_date,cus_name=cus_name, cus_type=cus_type,sales_vol=sales_vol,
                                     sales_num=sales_num,discount=discount,profit=profit,order_category=order_category,
                                     cate_items=cate_items,order_name=order_name,iss_date=iss_date,area=area,city=city)

    def get(self, request):
        return Response({'code': 20000,'flag':1, 'message': 'OK'})

    def post(self, request):
        print("********")
        user = request.user
        file_type = request.data.get("file_type")
        upload_file = request.FILES.get('file', None)
        print(file_type)
        print(upload_file)
        file_name = "%s_%s" % (int(time.time()), upload_file.name)
        sub_file_path = os.path.join('excel', file_name)
        file_path = os.path.join(settings.MEDIA_ROOT, sub_file_path)
        destination = open(file_path, 'wb+')
        for chunk in upload_file.chunks():
            destination.write(chunk)
        destination.close()
        obj = MyUploadFileRecord.objects.create(file_name=file_name, file_path=sub_file_path, user=user, file_type=file_type)
        fun_map = {'1': ImportExcelView.import_one, '2': ImportExcelView.import_two,
                   '3': ImportExcelView.import_three, '4': ImportExcelView.import_four,
                   '5': ImportExcelView.import_five, '6': ImportExcelView.import_six}

        fun_map[file_type](obj)
        return Response({'code': 20000, 'flag':1,'message': 'OK'})


class ClearTableDataView(APIView):

    def post(self, request):
        file_type = request.data.get("file_type")
        file_type = str(file_type)
        sql_map = {
            "1":SalesData, "2": CategoryAnalysis, "3":CustomerAnalysis,
            "4":LogisticsAnalysis, "5":DealerAnalysis, "6":OrderDetails
        }
        sql_map[file_type].objects.all().delete()
        return Response({'code': 20000,'flag':1, 'message': 'OK'})


class ShowTableDataView(APIView):

    def post(self, request):
        file_type = request.data.get("file_type", "")
        print(file_type)
        file_type = str(file_type)
        if file_type == "1":
            data = SalesDataSerializer(SalesData.objects.all(), many=True).data
        elif file_type == "2":
            data = CategoryAnalysisSerializer(CategoryAnalysis.objects.all(), many=True).data

        elif file_type == "3":
            data = CustomerAnalysisSerializer(CustomerAnalysis.objects.all(), many=True).data

        elif file_type == "4":
            data = LogisticsAnalysisSerializer(LogisticsAnalysis.objects.all(), many=True).data

        elif file_type == "5":
            data = DealerAnalysisSerializer(DealerAnalysis.objects.all(), many=True).data

        elif file_type == "6":
            data = OrderDetailsSerializer(OrderDetails.objects.all(), many=True).data
        else:
            data = []
        return Response({'code': 20000,'flag':1, 'message': '查询成功','data':data})


class TableListView(APIView):
    def get(self, request):
        data = [{'id': '1', 'name': 'sales_data', 'text': '销售数据概览表'},
                {'id': '2', 'name': 'category_analysis', 'text': '品类销售分析表'},
                {'id': '3', 'name': 'customer_analysis', 'text': '客户消费行为分析表'},
                {'id': '4', 'name': 'logistics_analysis', 'text': '物流配送分析表'},
                {'id': '5', 'name': 'dealer_analysis', 'text': '经销商绩效分析表'},
                {'id': '6', 'name': 'order_details', 'text': '订单详情分析表'}]
        return Response({'code': 20000,'flag':1, 'message': '查询成功','data':data})


class TableColumnListView(APIView):
    def get(self, request):
        model_name = request.query_params.get('model_name')
        model_cls = getattr(vislib.models, model_name)
        fields = model_cls._meta.fields
        fields = [{'type':item.__class__, 'name': item.column} for item in fields]
        return Response(fields)

class TableColumnDataListView(APIView):
    def get(self, request):
        ret = []
        model_name = request.query_params.get('model_name')
        model_cls = getattr(vislib.models, model_name)
        column1 = request.query_params.get('column1')
        column2 = request.query_params.get('column2')
        with connection.cursor() as cursor:
            sql = "select %s,%s from main.%s" % (column1,column2, model_cls._meta.db_table)
            cursor.execute(sql)
            for item in cursor.fetchall():
                ret.append({column1: item[0], column2:item[1]})
        return Response(ret)


class MyExcelListView(APIView):
    def get(self, request):
        data = []
        user = request.user
        obj_list = MyUploadFileRecord.objects.filter(user=user)
        for item in obj_list:
            data.append({
                'id': item.id,
                'upload_time': item.upload_time,
                'file': item.file_name,
                'file_path': item.file_path
            })
        return Response({'code': 20000,'flag':1, 'message': '查询成功','data':data})

    def delete(self, request):
        item_id = request.query_params.get("id")
        MyUploadFileRecord.objects.get(id=item_id).delete()
        return Response({'code': 20000, 'flag':1,'message': 'OK'})


class MyImageListView(APIView):
    def get(self, request):
        data = []
        user = request.user
        obj_list = MyImageRecord.objects.filter(user=user)
        for item in obj_list:
            data.append({
                'id': item.id,
                'upload_time': item.upload_time,
                'file': item.image_name,
                'file_path': item.image_path
            })
        return Response({'code': 20000,'flag':1, 'message': '查询成功','data':data})

    def post(self, request):
        user = request.user
        upload_file = request.FILES.get('file', None)
        print(upload_file)
        file_name = "%s_%s" % (int(time.time()), upload_file.name)
        print(file_name)
        sub_file_path = os.path.join('image', file_name)
        file_path = os.path.join(settings.MEDIA_ROOT, sub_file_path)
        destination = open(file_path, 'wb+')
        for chunk in upload_file.chunks():
            destination.write(chunk)
        destination.close()
        obj = MyImageRecord.objects.create(image_name=file_name, image_path=sub_file_path, user=user)
        return Response({'code': 20000, 'flag':1, 'message': 'OK'})

    def delete(self, request):
        item_id = request.query_params.get("id")
        MyImageRecord.objects.get(id=item_id).delete()
        return Response({'code': 20000, 'message': 'OK'})


class UserView(APIView):
    @method_decorator(ensure_csrf_cookie)
    def get(self, request):
        data = []
        obj_list = User.objects.all()
        for item in obj_list:
            data.append({
                'id': item.id,
                'register_date': item.date_joined,
                'username': item.username,
                'password': item.password,
                'email': item.email,
                'is_supervisor': str(item.is_superuser),
                'last_login_time': item.last_login
            })
        return Response({'code': 20000,'flag':1, 'message': '查询成功','data':data})

    def put(self, request):
        item_id = request.query_params.get("id")
        obj = User.objects.get(id=item_id)
        password = request.data.get("password")
        email = request.data.get("email")
        user_type = request.data.get("user_type")
        obj.set_password(password)
        obj.email = email
        if user_type in ['1', 1]:
            obj.is_superuser = 1
        obj.save()
        return Response({'code': 20000, 'flag':1,'message': 'OK'})
    
    def post(self, request):
        username = request.data.get("username")
        password = request.data.get("password")
        email = request.data.get("email")
        user_type = request.data.get("user_type")
        obj = User.objects.create(username=username,email=email)
        obj.set_password(password)
        obj.is_active=1
        if user_type in ['1', 1]:
            obj.is_superuser = 1
        obj.save()
        return Response({'code': 20000 , 'flag':1,'message': 'OK'})

    def delete(self, request):
        item_id = request.query_params.get("id")
        User.objects.get(id=item_id).delete()
        return Response({'code': 20000, 'flag':1,'message': 'OK'})